'''
--  @owner:opentestcase005
--  @date:2020/11/10
--  @testpoint:explain analyze分析CREATE TABLE AS语句
'''
import sys
import unittest
sys.path.append(sys.path[0]+"/../")
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH


logger = Logger()
commonsh = CommonSH('dbuser')
constant = Constant()

class SYS_Operation(unittest.TestCase):
    def setUp(self):
        logger.info('------------------------Opengauss_Function_DML_Set_Case0133开始执行-----------------------------')

    def test_explain(self):
        # 建表并插入数据
        sql_cmd1 = commonsh.execut_db_sql('''drop table if exists my_table;
      create table my_table (id int,name varchar(20));
      insert into my_table values(1,'Wiliian'),(2,'Nakli'),(3,'uisvc'),(4,'yuiy');''')
        logger.info(sql_cmd1)
        self.assertIn(constant.TABLE_CREATE_SUCCESS, sql_cmd1)
        self.assertIn(constant.INSERT_SUCCESS_MSG, sql_cmd1)
        # explain analyze语句执行delete语句;回滚（id为3的数据恢复）
        sql_cmd2 = commonsh.execut_db_sql('''explain CREATE TABLE my_table1 AS table  my_table;
        explain analyze CREATE TABLE my_table1 AS table my_table;
        start transaction;
        explain analyze CREATE TABLE my_table2 AS table my_table1;
        rollback;''')
        logger.info(sql_cmd2)
        self.assertIn(constant.EXPLAIN_FAIL_MSG, sql_cmd2)
        self.assertIn(constant.EXPLAIN_SUCCESS_MSG, sql_cmd2)
        self.assertIn(constant.START_TRANSACTION_SUCCESS_MSG, sql_cmd2)
        self.assertIn(constant.ROLLBACK_MSG, sql_cmd2)
        # 查询my_table2（不存在）
        sql_cmd3 = commonsh.execut_db_sql('''select * from my_table2;''')
        logger.info(sql_cmd2)
        self.assertIn('ERROR:  relation "my_table2" does not exist',sql_cmd3)

    # 清理环境
    def tearDown(self):
        logger.info('----------this is teardown-------')
        sql_cmd3 = commonsh.execut_db_sql('''drop table my_table;
        drop table my_table1;''')
        logger.info(sql_cmd3)
        logger.info('------------------------Opengauss_Function_DML_Set_Case0133执行结束--------------------------')
